Re: Question on locking - Mailing list pgsql-novice

Hi Terry,

I figure that if you have your table to track edited records and use
that in conjunction with Tom's approach you have a pretty good system
with only one major problem:

What happens when a user acquires an editable record but doesn't
release it because he gets disconnected disgraciously while editing
(somebody tripped over the ethernet cable)?

You're probably using your time stamp to help you get out of this
situation but you can use just these techniques to circumvent the
problem:

- at connection a client must release all his locks in case he was
disconnected disgraciously just before and had left some locks active.

- a client must release all his locks when disconnecting graciously.

- a client that encounters a record that is already being edited
should check whether the user who's editing it is still really
connected.

- all user names of connected users have to be unique.


If you don't mind transactions that last for the time a user edits a
record then you can go the way described in the pgSQL4RB manual as
mentioned a few posts before where you don't need any of the tables
and techniques outlined above.  Just do a query to verify the record
is unlocked and then acquire a lock on it with SELECT FOR UPDATE for
the duration of the editing process.  That's all.  (If your
transaction lasts too long, like a couple of hours in my tests, you
will loose it though so you might need some code for this situation.)

Marc



>I appreciate your reply. I, for one, am at the point where I have to make a
>decision regarding how best to handle locking with the tool that I have, that
>is, version 7.4. I raised this question several months ago, and the advice
>was to provide a table that would contain feedback that could be given to the
>user in a locking conflict. At present, I have a "tool kit" function that
>aquires a share lock on a give row when the user enters into "Edit" mode. In
>addition, a record is inserted into a table called lock which contains the
>user id, the pid, the table name, the oid of the record, and a time stamp. A
>unique index on the table name and the oid prevent simultaneous, duplicate
>entries. Also, built into the tookit functions, is code that checks for an
>existing table entry for the same table and row and reports back to the user,
>that "billy bob" has the record locked. Should I use this approach in
>conjunction with a much more narrow window in which the lock exists, as Tom's
>post indicated?
>
>I am interested any approach that will work best for the user, and ultimately,
>for me.
>
>
>On Friday 05 November 2004 05:50 am, M. Bastin saith:
>>  I don't agree with Tom's approach because it can only work in a very
>>  limited number of cases. Imagine 3 persons editing the same record at
>>  about the same time.
>>
>>  The second one to update has to solve the conflict with the first.
>>  In cases where this is done manually and not automatically, which
>>  would be the safest thing to do in most cases I guess(1), you can
>>  even imagine person 2 calling person 1 for clarifications etc.
>>
>>  In the mean time person 3 wants to update but is also confronted with
>>  conflicts from person 1's update and starts working on solving them.
>>  When he's done he gets another conflict again (if the program is well
>>  made) because in the mean time person 2 has resolved his conflict and
>>  committed his update.  Person 3 can start over with this newest
>>  conflict.
>>
>>  At the end of the road there's been a lot of confusion, a huge waste
>>  of man-hours and a disgruntled employer who has to pay the wages for
>>  this.
>>
>>  And the risk if the program is not well made is that person 3 will
>>  only notice the conflict with person 1 but not with person 2!
>>
>>  Locking other (human) users out of a record that is being updated is
>>  the only fool-proof method I have ever been able to imagine.
>>  PostgreSQL lacks in this aspect.  If it's not good to have lengthy
>>  transactions for all sorts of reasons then PostgreSQL needs to come
>  > up with some command 'LOCK TILL UPDATE' that works outside
>>  transactions.
>>
>>  my 2 cents,
>>
>>  Marc
>>
>>  (1) imagine the one person has changed the prefix of a phone number
>>  and the second one changed the extension in the same field.  An
>>  automatic conflict solver can't cope with this.  A manual conflict
>>  solver would need to show the original record, the changes made by
>>  the one user, those made by the other, and if a third user comes in
>>  his changes as well etc.  This would make for a very confusing and
>>  cumbersome interface and huge complications for the developer.  The
>>  only elegant way out is locking users out of records and have them do
>>  their edits one by one, and not all at the same time.
>>
>>  >Greetings:
>>  >
>>  >I posted a question regarding this issue about 2 weeks ago. See "Question
>>  >Regarding Locks" from 10/27/04. Tom Lane resonded with the following:
>>  >
>>  >"To me, this says that you're already off on the wrong foot.
>>  >
>>  >You don't ever want your client application holding locks while a
>>  >human user edits text, drinks coffee, goes out to lunch, or whatever.
>>  >A better design is to fetch the data without locking it, allow the
>>  >user to edit as he sees fit, and then when he clicks "save" you do
>>  >something like
>>  >
>>  >         begin;
>>  >         select row for update;
>>  >         if [ row has not changed since you originally pulled it ] then
>>  >                 update row with changed values;
>>  >                 commit;
>>  >         else
>>  >                 abort;
>>  >                 notify user of conflicts
>>  >                 let user edit new data to resolve conflicts and try again
>>  >         fi
>>  >
>>  >In this design the row lock is only held for milliseconds.
>>  >
>>  >You need to provide some code to let the user merge what he did with the
>>  >prior changes, so that he doesn't have to start over from scratch in the
>>  >failure case.  What "merge" means requires some business-logic knowledge
>>  >so I can't help you there, but this way you are spending your effort on
>>  >something that actually helps the user, rather than just tells him he
>>  >has to wait.  Performance will be much better too --- long-lasting
>>  >transactions are nasty for all sorts of reasons.
>>  >
>>  >BTW, a handy proxy for "row has not changed" is to see if its XMIN
>>  >system column is still the same as before.  If so, no transaction has
>>  >committed an update to it.  (This may or may not help much, since you're
>>  >probably going to end up groveling over all the fields anyway in the
>>  >"notify user" part, but it's a cool hack if you can use it.)
>>  >
>>  >                         regards, tom lane"
>>  >
>>  >I have carefully considered his advice and I will be implementing his
>>  >suggestions within a couple weeks.
>>  >
>>  >Thanks...
>>  >
>>  >On Friday 05 November 2004 02:36 am, Steve Tucknott saith:
>>  >  > PostGreSQL 7.4.5
>>  >>
>>  >>  If I have the situation where process 1 has selected record1 from table
>>  >>  a for update and then process 2 tries to do the same, am I right in
>>  >>  assuming that process 2 will wait until the first process completes the
>>  >>  transaction (I've looked at Chapter 12 and this is intimated).
>>  >>  How can I detect the lock on process 2? I want to be able to tell the
>>  >>  user that the row is tentatively locked and to allow them to abort the
>>  >>  update attempt. I can't see a 'SET LOCK MODE TO NOT WAIT' style
>>  >> command, so how do I stop process 2 from waiting?
>>  >>  Is the suggested route to interrogate the system tables prior to
>>  >>  selecting for update, to see if a lock has been applied?
>>  >>
>>  >>  Normally we wait on locks , so this is not an issue.
>>  >>
>>  >>
>>  >>  Regards,
>>  >>
>>  >>  Steve Tucknott
>>  >>
>>  >>  ReTSol Ltd
>>  >>
>>  >>  DDI: 01903 828769
>>  >
>>  >--
>>  >
>>  >  Work: 1-336-372-6812
>>  >  Cell: 1-336-363-4719
>>  >email: terry@esc1.com
>>  >
>>  >---------------------------(end of broadcast)---------------------------
>>  >TIP 7: don't forget to increase your free space map settings
>>
>>  ---------------------------(end of broadcast)---------------------------
>>  TIP 4: Don't 'kill -9' the postmaster
>
>--
>  Work: 1-336-372-6812
>  Cell: 1-336-363-4719
>email: terry@esc1.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend